package org.usts.trainning.supermarket.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.usts.trainning.supermarket.entity.Bill;
import org.usts.trainning.supermarket.util.DBUtilities;

public class BillDao {
	final String configFile = "jdbc.properties";
	// 配置文件
	private Connection conn = null;
	String tableName = null;

	/** * 获得数据库连接 */
	private Connection getConnection() {
		// 获得数据库连接
		// 取得配置文件存储的路径,此外约定文件是存放在项目的“根”下
		// 注意路径中不要含有中文。
		if (conn == null) {
			String strPath = this.getClass().getResource("/jdbc.properties").getPath();
			conn = DBUtilities.connect(strPath);
		}
		return conn;
	}

	/** * @param */
	public BillDao() {
		getConnection();
		if (conn == null) {
			try {
				throw new SQLException("无法连接数据库！");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	// 查看所有用户信息
	public List<Bill> findAllBill() {
		// TODO Auto-generated method stub
		List<Bill> list = null;
		String[] params = null;
		tableName = "bill";
		// 1.判断数据库的连接是否已经获取
		if (conn != null) {
			// 2.准备 SQL 语句
			String sql = "Select * From " + tableName;
			params = new String[] {};
			ResultSet rs = null;
			try {
				// 3.执行 SQL 语句
				rs = DBUtilities.querySQL(conn, sql, params); // 4.处理查询结果
				if (rs != null) {
					list = new ArrayList<Bill>();
					while (rs.next()) {
						Bill bill = new Bill();
						bill.setBid(rs.getInt(1));
						bill.setBcode(rs.getString(2));
						bill.setBname(rs.getString(3));
						bill.setPsupplier(rs.getString(4));
						bill.setBcount(rs.getInt(5));
						bill.setBmoney(rs.getDouble(6));
						bill.setBflag(rs.getString(7));
						bill.setBtime(rs.getString(8));
						list.add(bill);

					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
		}
		return list;
	}

	// 根据id查找
	public Bill findBillById(Integer bid) {
		// TODO Auto-generated method stub
		Bill bill = null;
		String[] params = null;
		tableName = "bill";
		// 1.判断数据库的连接是否已经获取
		if (conn != null) {
			// 2.准备 SQL 语句
			String sql = "Select * From " + tableName + " where bid=?";
			params = new String[] { String.valueOf(bid) };
			ResultSet rs = null;
			try {
				// 3.执行 SQL 语句
				rs = DBUtilities.querySQL(conn, sql, params); // 4.处理查询结果
				if (rs != null) {
					while (rs.next()) {
						bill = new Bill();
						bill.setBid(rs.getInt(1));
						bill.setBcode(rs.getString(2));
						bill.setBname(rs.getString(3));
						bill.setPsupplier(rs.getString(4));
						bill.setBcount(rs.getInt(5));
						bill.setBmoney(rs.getDouble(6));
						bill.setBflag(rs.getString(7));
						bill.setBtime(rs.getString(8));

					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
		}
		return bill;
	}

	// 根据id编辑用户信息
	public Boolean updateBillById(Bill bill) {
		boolean result = false;
		String[] params = null;
		tableName = "bill";
		// 1.判断数据库的连接是否已经获取
		if (conn != null) {
			// 2.准备 SQL 语句，及参数
			String sql = "update " + tableName
					+ " set bcode=?,bname=?,psupplier=?,bcount=?,bmoney=?,bflag=?,btime=? where bid=?";

			params = new String[] { bill.getBcode(), String.valueOf(bill.getBname()), bill.getPsupplier(),
					String.valueOf(bill.getBcount()), String.valueOf(bill.getBmoney()), bill.getBflag(),
					bill.getBtime(), String.valueOf(bill.getBid()) };
			// 3.执行查询的 SQL语句
			result = DBUtilities.updateSQL(conn, sql, params);
		}
		return result;
	}

	// 根据id删除用户
	public Boolean deleteUser(Integer bid) {
		boolean result = false;
		String[] params = null;
		tableName = "bill";
		// 1.判断数据库的连接是否已经获取
		if (conn != null) {
			// 2.准备 SQL 语句，及参数
			String sql = "DELETE FROM " + tableName + " where bid=?";
			params = new String[] { String.valueOf(bid) };
			// 3.执行查询的 SQL语句
			result = DBUtilities.updateSQL(conn, sql, params);
		}
		return result;
	}

	// 增加账单
	public Boolean addBill(Bill bill) {
		boolean result = false;
		String[] params = null;
		tableName = "bill";
		// 1.判断数据库的连接是否已经获取
		if (conn != null) {
			// 2.准备 SQL 语句，及参数
			String sql = "Insert Into " + tableName
					+ " (bcode,bname,psupplier,bcount,bmoney,bflag,btime) value(?,?,?,?,?,?,?)";
			params = new String[] { bill.getBcode(), bill.getBname(), bill.getPsupplier(),
					String.valueOf(bill.getBcount()), String.valueOf(bill.getBmoney()), bill.getBflag(),
					bill.getBtime() };
			// 3.执行查询的 SQL语句
			result = DBUtilities.updateSQL(conn, sql, params);
		}
		return result;
	}

	// 多条件查询
	public List<Bill> searchBill(Bill b) {
		// TODO Auto-generated method stub
		List<Bill> list = null;
		String[] params = null;
		tableName = "bill";
		// 1.判断数据库的连接是否已经获取
		if (conn != null) {
			// 2.准备 SQL 语句
			String sql = "Select * From " + tableName + " where bname=? and psupplier=? and bflag=?";
			params = new String[] { b.getBname(), b.getPsupplier(), b.getBflag() };
			ResultSet rs = null;
			try {
				// 3.执行 SQL 语句
				rs = DBUtilities.querySQL_2(conn, sql, params); // 4.处理查询结果
				if (rs != null) {
					list = new ArrayList<Bill>();
					while (rs.next()) {
						Bill bill = new Bill();
						bill.setBid(rs.getInt(1));
						bill.setBcode(rs.getString(2));
						bill.setBname(rs.getString(3));
						bill.setPsupplier(rs.getString(4));
						bill.setBcount(rs.getInt(5));
						bill.setBmoney(rs.getDouble(6));
						bill.setBflag(rs.getString(7));
						bill.setBtime(rs.getString(8));
						list.add(bill);

					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
		}
		return list;
	}

	// 关闭数据库连接
	public void close() {
		try {
			if (conn != null)
				conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

}
